"""
作者：元明
日期：2023年11月20日
"""

from flask import *
from pymysql import *

# 声明蓝图对象
stuBP = Blueprint(name="stu", import_name=__name__)

# 连接数据库
db_connect = Connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123123123",
    database="sms",
    charset="utf8")


# 定义一个首页接口
@stuBP.route("/")
def index():
    return render_template("index.html")


# 定义一个用来返回所有学生数据的接口
@stuBP.route("/list")
def list():
    # 获取游标对象
    cursor = db_connect.cursor()
    # 获取所有学生数据的 SQL 语句
    sql_str = '''select * from student'''
    # 执行 sql
    cursor.execute(sql_str)
    # 获取所有数据
    data = cursor.fetchall()
    # 准备一个空列表
    datas = []
    for item in data:
        s = {}
        s["sid"] = item[0]
        s["name"] = item[1]
        s["age"] = item[2]
        s["gender"] = item[3]
        datas.append(s)
    cursor.close()
    return datas


# 涉及添加新的接口
@stuBP.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "GET":
        return render_template("add.html")
    else:
        # 获取游标对象
        cursor = db_connect.cursor()
        # 添加数据
        sql_str = '''insert into student (name,age,gender) values(%s,%s,%s)'''
        # 提取请求中的数据
        name = request.values.get("name")
        age = request.values.get("age")
        gender = request.values.get("gender")
        # 执行 sql
        cursor.execute(sql_str, [name, age, gender])
        # 提交修改操作
        db_connect.commit()
        # 关闭游标
        cursor.close()
        return redirect("/")


# 修改数据接口
@stuBP.route("change/<sid>", methods=["GET", "POST"])
def change(sid):
    if request.method == "GET":
        return render_template("change.html")
    else:
        cursor = db_connect.cursor()
        sql_str = '''update student set name=%s,age=%s,gender=%s where sid = %s'''
        name = request.values.get("name")
        age = request.values.get("age")
        gender = request.values.get("gender")
        cursor.excute(sql_str, [name, age, gender, sid])
        db_connect.commit()
        cursor.close()
        return redirect("/")


# 修改数据时，用来获取回显数据的接口
@stuBP.route("/changequery/<sid>")
def changequery(sid):
    cursor = db_connect.cursor()
    sql_str = f'''select * from student where sid ={sid}'''
    cursor.excute(sql_str)
    item = cursor.fetcone()
    cursor.close()
    s = {}
    s["sid"] = item[0]
    s["name"] = item[1]
    s["age"] = item[2]
    s["gender"] = item[3]
    return s


# 删除接口
@stuBP.route("/delete/<sid>")
def delete(sid):
    curson = db_connect.cursor()
    sql_str = f'''delete from student where sid={sid}'''
    curson.execute(sql_str)
    curson.close()
    db_connect.commit()
    return redirect("/")
